from datetime import datetime
import os
import sqlite3
from typing import TypedDict

class Video(TypedDict):
    id: int
    name: str
    url: str
    status: int     # 1:下载中，2:下载成功 3:下载失败
    storepath: str
    created_at: str




class VideoDao:
    def __init__(self,dbname):
        cwd = os.getcwd()
        self.dbname = dbname
        self.dbpath = os.path.join(cwd, dbname)
        self.connectDB()

    def connectDB(self):
        connect = sqlite3.connect(self.dbpath, isolation_level=None, check_same_thread=False)
        cursor = connect.cursor()
        self.cursor = cursor
        cursor.execute("""CREATE TABLE IF NOT EXISTS videos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                url Text,
                status INTEGER,
                storepath Text,
                created_at TEXT
        )""")

    def insertVideo(self,info:Video) ->Video:
        now = datetime.now()
        info["created_at"] = now.strftime("%Y-%m-%d %H:%M:%S")
        self.cursor.execute("insert into videos (name, url, status,storepath,created_at) values (?, ?, ?, ?,?)",(info['name'],info['url'],info['status'],info['storepath'],info['created_at']))
        info["id"] = self.cursor.lastrowid
        return info

    def updateVideoByName(self,info:Video) ->None:
        self.cursor.execute("update videos set status = ? where name = ?",(info['status'],info['name']))

    def deleteVideoByName(self,name:str)->None:
        self.cursor.execute("delete from videos where name = ?",(name,))
    def deleteVideoById(self,id:int)->None:
        self.cursor.execute("delete from videos where id = ?",(id,))

    # 获取列表
    def getVideoListByStatus(self,status:int =0)->list:
        if status == 0:
            self.cursor.execute("select * from videos order by id desc")
            columns = [des[0] for des in self.cursor.description]
            rows = self.cursor.fetchall()
            result = [dict(zip(columns,row)) for row in rows]
            return result
        execute = self.cursor.execute("select * from videos where status = ? order by id desc", (status,))
        columns = [ des[0] for des in execute.description]
        rows = execute.fetchall()
        result = [dict(zip(columns,row)) for row in rows]
        return result

    #状态检验
    def getVideoByUrlAndStatus(self,url:str,*status)->Video|None:

        statusPlaceholder = ",".join("?" * len(status))

        query = f"select * from videos where url = ? and status in ({statusPlaceholder})"
        args = (url,) + status
        execute = self.cursor.execute(query, args )
        fetchone = execute.fetchone()
        if fetchone is None:
            return None
        columns = [des[0] for des in self.cursor.description]
        return dict(zip(columns,fetchone))
